Isnumeric function?

Isnumeric function?

am 07.09.2004 07:44:21 von Theo.Galanakis

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C4949D.B948D170
Content-Type: text/plain


How could you determine if a value being inserted into a varchar column is
numeric?

I was thinking of using a Regular expression to find this, something like

... Where content ~* '^[0-9]{1,10}'

There must be an easier way like a isNumeric() function?

Theo


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C4949D.B948D170
Content-Type: text/html






Isnumeric function?



How could you determine if a value being inserted into a varchar column is numeric?



I was thinking of using a Regular expression to find this, something like



.. Where content ~* '^[0-9]{1,10}'



There must be an easier way like a isNumeric() function?



Theo





____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.

------_=_NextPart_001_01C4949D.B948D170--

Re: Isnumeric function?

am 07.09.2004 08:05:26 von Olly

On Tue, 2004-09-07 at 06:44, Theo Galanakis wrote:
> How could you determine if a value being inserted into a varchar
> column is numeric?
>
> I was thinking of using a Regular expression to find this, something
> like
>
> .. Where content ~* '^[0-9]{1,10}'

~ '^[0-9]+$'

Your version only checks the beginning of the string.

> There must be an easier way like a isNumeric() function?

Not that I know of.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"For whosoever shall call upon the name of the Lord
shall be saved." Romans 10:13



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Isnumeric function?

am 08.09.2004 18:47:17 von Josh

Oliver, Theo:

> ~ '^[0-9]+$'

Actually, I usually do:

~ '^[0-9]+\.?[0-9]*$'

.... to include decimals. However, the above assumes that there is at least a
"0" before the decimal; it would be nice to adapt it to matching a leading
decimal (i.e. .057 ) as well. Can't see any easy way, though ...

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Isnumeric function?

am 08.09.2004 19:20:17 von Olly

On Wed, 2004-09-08 at 17:47, Josh Berkus wrote:
> Oliver, Theo:
>
> > ~ '^[0-9]+$'
>
> Actually, I usually do:
>
> ~ '^[0-9]+\.?[0-9]*$'
>
> ... to include decimals. However, the above assumes that there is at least a
> "0" before the decimal; it would be nice to adapt it to matching a leading
> decimal (i.e. .057 ) as well. Can't see any easy way, though ...

~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Put on the whole armor of God, that ye may be able to
stand against the wiles of the devil."
Ephesians 6:11


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Isnumeric function?

am 08.09.2004 19:48:01 von Josh

Theo, Oliver,

> Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?

Yes, because it also matches "." , which is not a valid numeric value.

> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'

Ah, the brute force approach ;-)

Actually, the above could be written:

~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'

.... though that still seems inelegant to me. Is there a regex expert in the
house?

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Isnumeric function?

am 08.09.2004 20:11:51 von Olly

On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> Theo, Oliver,
>
> > Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
>
> Yes, because it also matches "." , which is not a valid numeric value.
>
> > ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
>
> Ah, the brute force approach ;-)

Nothing like using a nice big hammer!

> Actually, the above could be written:
>
> ~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'

But that doesn't allow a trailing decimal point.

>
> ... though that still seems inelegant to me. Is there a regex expert in the
> house?

All the elegant approaches I can think of match the empty string. There
must be at least one digit and 0 or 1 decimal point with no other
characters permitted. If you use this as a constraint, you could make
it elegant and combine it with another constraint to exclude '' and '.'.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Put on the whole armor of God, that ye may be able to
stand against the wiles of the devil."
Ephesians 6:11


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Isnumeric function?

am 09.09.2004 01:39:15 von Theo.Galanakis

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C495FD.0D5E12B0
Content-Type: text/plain


Thankyou all for your feedback. I actually only want to check for whole
numbers, so the ~ '^[0-9]+$' expression is good.

The issue really is that our CMS system sometimes holds the value of primary
keys within a "content" varchar column(don't ask!), which is a nightmare to
search across. I tried applying an index across the "content" varchar column
and it failed.
error: btree item size 2744 exceeds maximum 2713.

I assume I had to change some server settings to extend the maximum, however
in the end this column holds content, and even applying an index would be
incredible slow to search across hundred of thousands of "content" records
looking for a primary key.

So I came up with the following. A Insert/update trigger would call a
procedure to check to see if the content is numeric(a whole number), if so
would update an indexed integer column called (content_numeric). Which would
be the base column to search appon.


Here is the function anyway:
CREATE OR REPLACE FUNCTION update_content_node()
RETURNS trigger AS
'
begin
/* New function body */
IF NEW.content ~ \'^[0-9]+$\' THEN
NEW.content_numeric := NEW.content;
ELSE
NEW.content_numeric := null;
END IF;
RETURN NEW;
end;
'
LANGUAGE 'plpgsql' IMMUTABLE;


Does anyone have any better suggestions???

Theo





____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C495FD.0D5E12B0
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable






RE: [SQL] Isnumeric function?



Thankyou all for your feedback. I actually only want to c=
heck for whole numbers, so the ~ '^[0-9]+$' expression is good.



The issue really is that our CMS system sometimes holds t=
he value of primary keys within a "content" varchar column(don't =
ask!), which is a nightmare to search across. I tried applying an index acr=
oss the "content" varchar column and it failed.



        error: btree i=
tem size 2744 exceeds maximum 2713.



I assume I had to change some server settings to extend t=
he maximum, however in the end this column holds content, and even applying=
an index would be incredible slow to search across hundred of thousands of=
"content" records looking for a primary key.



So I came up with the following. A Insert/update trigger =
would call a procedure to check to see if the content is numeric(a whole nu=
mber), if so would update an indexed integer column called (content_numeric=
). Which would be the base column to search appon.





Here is the function anyway:

CREATE OR REPLACE FUNCTION update_content_node()

  RETURNS trigger AS

'

begin

  /* New function body */

  IF NEW.content ~ \'^[0-9]+$\' THEN

     NEW.content_numeric :=3D NEW.co=
ntent;

  ELSE

     NEW.content_numeric :=3D null;<=
/FONT>

  END IF;

  RETURN NEW;

end;

'

  LANGUAGE 'plpgsql' IMMUTABLE;





Does anyone have any better suggestions???



Theo









____________________=
__________________________________________________
This email, including=
attachments, is intended only for the addressee
and may be confidential=
, privileged and subject to copyright. If you
have received this email =
in error, please advise the sender and delete
it. If you are not the in=
tended recipient of this email, you must not
use, copy or disclose its c=
ontent to anyone. You must not copy or
communicate to others content t=
hat is confidential or subject to
copyright, unless you have the consen=
t of the content owner.

------_=_NextPart_001_01C495FD.0D5E12B0--

Re: Isnumeric function?

am 09.09.2004 01:57:20 von tgl

Theo Galanakis writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number), if so
> would update an indexed integer column called (content_numeric). Which would
> be the base column to search appon.

> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;

Hmm. Seems like you could get burnt by "content" that is by chance a
long string of digits --- you'd get an integer overflow error at the
attempt to assign to content_numeric. Can you make an assumption that
indexable keys are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Isnumeric function?

am 09.09.2004 02:02:54 von Theo.Galanakis

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C49600.5B2ABB30
Content-Type: text/plain


Thanks Tom,
Actually I did not attach the latest function, I did have a limit of 9
numerical characters, found that out when I applied the update to move all
current numerical values to that column.

Theo

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, 9 September 2004 9:57 AM
To: Theo Galanakis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?


Theo Galanakis writes:
> So I came up with the following. A Insert/update trigger would call a
> procedure to check to see if the content is numeric(a whole number),
> if so would update an indexed integer column called (content_numeric).
> Which would be the base column to search appon.

> CREATE OR REPLACE FUNCTION update_content_node()
> RETURNS trigger AS
> '
> begin
> /* New function body */
> IF NEW.content ~ \'^[0-9]+$\' THEN
> NEW.content_numeric := NEW.content;
> ELSE
> NEW.content_numeric := null;

Hmm. Seems like you could get burnt by "content" that is by chance a long
string of digits --- you'd get an integer overflow error at the attempt to
assign to content_numeric. Can you make an assumption that indexable keys
are at most 9 digits? If so then
IF NEW.content ~ \'^[0-9]{1,9}$\' THEN
Or use a bigint column and crank up the number of digits appropriately.

regards, tom lane


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C49600.5B2ABB30
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable






RE: [SQL] Isnumeric function?



Thanks Tom,

Actually I did not attach the latest function, I did hav=
e a limit of 9 numerical characters, found that out when I applied the upda=
te to move all current numerical values to that column.



Theo



-----Original Message-----

From: Tom Lane [mai=
lto:tgl@sss.pgh.pa.us]

Sent: Thursday, 9 September 2004 9:57 AM

To: Theo Galanakis

Cc: pgsql-sql@postgresql.org

Subject: Re: [SQL] Isnumeric function?





Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>=
writes:

> So I came up with the following. A Insert/update tr=
igger would call a

> procedure to check to see if the content is numeric=
(a whole number),

> if so would update an indexed integer column called=
(content_numeric).

> Which would be the base column to search appon. NT>



> CREATE OR REPLACE FUNCTION update_content_node() NT>

>   RETURNS trigger AS

> '

> begin

>   /* New function body */

>   IF NEW.content ~ \'^[0-9]+$\' THEN T>

>      NEW.content_numeric :=
=3D NEW.content;

>   ELSE

>      NEW.content_numeric :=
=3D null;



Hmm.  Seems like you could get burnt by "conten=
t" that is by chance a long string of digits --- you'd get an integer =
overflow error at the attempt to assign to content_numeric.  Can you m=
ake an assumption that indexable keys are at most 9 digits?  If so the=
n



        IF NEW.content=
~ \'^[0-9]{1,9}$\' THEN

Or use a bigint column and crank up the number of digits=
appropriately.



             =
;           reg=
ards, tom lane





____________________=
__________________________________________________
This email, including=
attachments, is intended only for the addressee
and may be confidential=
, privileged and subject to copyright. If you
have received this email =
in error, please advise the sender and delete
it. If you are not the in=
tended recipient of this email, you must not
use, copy or disclose its c=
ontent to anyone. You must not copy or
communicate to others content t=
hat is confidential or subject to
copyright, unless you have the consen=
t of the content owner.

------_=_NextPart_001_01C49600.5B2ABB30--

Re: Isnumeric function?

am 09.09.2004 03:58:24 von tswan

Oliver Elphick wrote:

>On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
>
>
>>Theo, Oliver,
>>
>>
>>
>>>Any reason why you don't like ~ '^([0-9]?)+\.?[0-9]*$' ?
>>>
>>>
>>Yes, because it also matches "." , which is not a valid numeric value.
>>
>>
>>
>>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
>>>
>>>
>>Ah, the brute force approach ;-)
>>
>>
>
>Nothing like using a nice big hammer!
>
>
>
Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a little cleaner?

>>Actually, the above could be written:
>>
>>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
>>
>>
>
>But that doesn't allow a trailing decimal point.
>
>
>
>>... though that still seems inelegant to me. Is there a regex expert in the
>>house?
>>
>>
>
>All the elegant approaches I can think of match the empty string. There
>must be at least one digit and 0 or 1 decimal point with no other
>characters permitted. If you use this as a constraint, you could make
>it elegant and combine it with another constraint to exclude '' and '.'.
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Isnumeric function?

am 09.09.2004 05:48:18 von gsstark

Theo Galanakis writes:

> error: btree item size 2744 exceeds maximum 2713.
>
> I assume I had to change some server settings to extend the maximum, however

I would guess the block size. But I'm just guessing.

> in the end this column holds content, and even applying an index would be
> incredible slow to search across hundred of thousands of "content" records
> looking for a primary key.

Perhaps you could have an indexed column that contains a crc32 hash? Then you
could do searches by comparing crc32 which make for fast efficient integer
index lookups. You should still include a comparison against the original
content column since it is possible for there to be a rare crc32 collision.

This doesn't let you do range lookups efficiently. But it does let you look up
specific values quickly even when they aren't numeric.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Isnumeric function?

am 09.09.2004 16:02:06 von jeff_eckermann

Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)

create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[ 0-9]*\\.?[0-9]+)-?)\'
'language 'sql';

--- Thomas Swan wrote:

> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Isnumeric function?

am 09.09.2004 17:01:38 von Vadim.Passynkov

How about this

CREATE OR REPLACE FUNCTION is_numeric ( text ) RETURNS bool AS '
if { [string is integer $1] || [string is double $1] } {
return true
}
return false
' LANGUAGE 'pltcl' IMMUTABLE;


SELECT is_numeric ( '-1' );
is_numeric
------------
t
(1 row)

SELECT is_numeric ( '+1e-1' );
is_numeric
------------
t
(1 row)

SELECT is_numeric ( '1.1.1' );
is_numeric
------------
f
(1 row)

--
Vadim Passynkov


-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Thursday, September 09, 2004 10:02 AM
To: Thomas Swan; olly@lfix.co.uk
Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?


Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)

create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[ 0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';

--- Thomas Swan wrote:

> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Isnumeric function?

am 09.09.2004 20:46:14 von Josh

Theo,

> Does anyone have any better suggestions???

Well, one suggestion would be to take a machete to your application. Putting
key references and text data in the same column? Sheesh.

If that's not an option, in addition to the approach you've taken, you could
also do a partial index on the appropriate numeric values:

CREATE INDEX idx_content_numeric ON botched_table(content)
WHERE content ~ '^[0-9]{1,9}$';

However, this approach may be more/less effective that the segregation
approach you've already taken.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Isnumeric function?

am 10.09.2004 02:20:44 von Theo.Galanakis

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C496CC.0342F0F0
Content-Type: text/plain


I was just thinking, wouldn't it be great if the pg community had a site
where anyone could contribute their generic functions, or request for a
particular function.

Cold Fusion has a cflib.org, perhaps a pglib.org?


-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Friday, 10 September 2004 12:02 AM
To: Thomas Swan; olly@lfix.co.uk
Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?


Ok, how about this. At least it works in my testing.
I have extended it to allow a negative sign (trailing
also), which I would expect to be allowed in a
comprehensive "isnumeric" function. If I am wrong,
feel free to slap me around; although correcting the
regex would be more constructive. ;-)

create function isnumeric(text) returns boolean as '
select $1 ~
\'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0-9]+\\.?[0-9]*|[ 0-9]*\\.?[0-9]+)
-?)\'
'language 'sql';

--- Thomas Swan wrote:

> Oliver Elphick wrote:
>
> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:
> >
> >
> >>Theo, Oliver,
> >>
> >>
> >>
> >>>Any reason why you don't like ~
> '^([0-9]?)+\.?[0-9]*$' ?
> >>>
> >>>
> >>Yes, because it also matches "." , which is not a
> valid numeric value.
> >>
> >>
> >>
> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+)$'
> >>>
> >>>
> >>Ah, the brute force approach ;-)
> >>
> >>
> >
> >Nothing like using a nice big hammer!
> >
> >
> >
> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$" be a
> little cleaner?
>
> >>Actually, the above could be written:
> >>
> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'
> >>
> >>
> >
> >But that doesn't allow a trailing decimal point.
> >
> >
> >
> >>... though that still seems inelegant to me. Is
> there a regex expert in the
> >>house?
> >>
> >>
> >
> >All the elegant approaches I can think of match the
> empty string. There
> >must be at least one digit and 0 or 1 decimal point
> with no other
> >characters permitted. If you use this as a
> constraint, you could make
> >it elegant and combine it with another constraint
> to exclude '' and '.'.
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C496CC.0342F0F0
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable






RE: [SQL] Isnumeric function?



I was just thinking, wouldn't it be great if the pg commu=
nity had a site where anyone could contribute their generic functions, or r=
equest for a particular function.



Cold Fusion has a cflib.org, perhaps a pglib.org?





-----Original Message-----

From: Jeff Eckermann [ ahoo.com">mailto:jeff_eckermann@yahoo.com]

Sent: Friday, 10 September 2004 12:02 AM

To: Thomas Swan; olly@lfix.co.uk

Cc: Josh Berkus; Theo Galanakis; pgsql-sql@postgresql.or=
g

Subject: Re: [SQL] Isnumeric function?





Ok, how about this.  At least it works in my testing=
..

I have extended it to allow a negative sign (trailing ONT>

also), which I would expect to be allowed in a

comprehensive "isnumeric" function.  If I=
am wrong,

feel free to slap me around; although correcting the NT>

regex would be more constructive. ;-)



create function isnumeric(text) returns boolean as ' T>

select $1 ~ \'(-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)|([0=
-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)-?)\'

'language 'sql';



--- Thomas Swan <tswan@idigx.com> wrote:



> Oliver Elphick wrote:

>

> >On Wed, 2004-09-08 at 18:48, Josh Berkus wrote:=


> > 

> >

> >>Theo, Oliver,

> >>

> >>   

> >>

> >>>Any reason why you don't like  ~ FONT>

> '^([0-9]?)+\.?[0-9]*$' ?

> >>>     

> >>>

> >>Yes, because it also matches "." =
, which is not a

> valid numeric value.

> >>

> >>   

> >>

> >>> ~ '^([0-9]+|[0-9]+\\.[0-9]*|[0-9]*\\.[=
0-9]+)$'

> >>>     

> >>>

> >>Ah, the brute force approach ;-)

> >>   

> >>

> >

> >Nothing like using a nice big hammer!

> >

> > 

> >

> Would "^([0-9]+\\.{0,1}[0-9]*|\\.[0-9]+)$"=
; be a

> little cleaner?

>

> >>Actually, the above could be written:

> >>

> >>~ '^([0-9]+)|([0-9]*\\.[0-9]+)$'

> >>   

> >>

> >

> >But that doesn't allow a trailing decimal point=
..

> >

> > 

> >

> >>... though that still seems inelegant to me=
..  Is

> there a regex expert in the

> >>house?

> >>   

> >>

> >

> >All the elegant approaches I can think of match=
the

> empty string. There

> >must be at least one digit and 0 or 1 decimal p=
oint

> with no other

> >characters permitted.  If you use this as =
a

> constraint, you could make

> >it elegant and combine it with another constrai=
nt

> to exclude '' and '.'.

> > 

> >

>

>

> ---------------------------(end of

> broadcast)---------------------------

> TIP 6: Have you searched our list archives?

>

>        &nbs=
p;       l.org" TARGET=3D"_blank">http://archives.postgresql.org

>







             =
;  =0D

__________________________________

Do you Yahoo!?

Yahoo! Mail - 50x more storage than other providers! HREF=3D"http://promotions.yahoo.com/new_mail" TARGET=3D"_blank">http://prom=
otions.yahoo.com/new_mail





____________________=
__________________________________________________
This email, including=
attachments, is intended only for the addressee
and may be confidential=
, privileged and subject to copyright. If you
have received this email =
in error, please advise the sender and delete
it. If you are not the in=
tended recipient of this email, you must not
use, copy or disclose its c=
ontent to anyone. You must not copy or
communicate to others content t=
hat is confidential or subject to
copyright, unless you have the consen=
t of the content owner.

------_=_NextPart_001_01C496CC.0342F0F0--

Re: Isnumeric function?

am 10.09.2004 04:21:53 von Josh

Theo,

> I was just thinking, wouldn't it be great if the pg community had a site
> where anyone could contribute their generic functions, or request for a
> particular function.

In theory, this is supposed to be a feature of pgFoundry.org. However, there
is a bug in gForge that prevents us from using it right now, and fixing the
bug is complicated.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Isnumeric function?

am 10.09.2004 04:50:27 von Theo.Galanakis

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C496E0.EDB48D10
Content-Type: text/plain



Josh,

I agree with the machete technique, unfortunately The structure is inplace
and a work-around was required.

I created the Index you specified, however it chooses to run a seq scan on
the column rather than a Index scan. How can you force it to use that
Index..

CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';

select * from botched_table where content = 200::integer

Theo
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; pgsql-sql@postgresql.org
Subject: Re: [SQL] Isnumeric function?


Theo,

> Does anyone have any better suggestions???

Well, one suggestion would be to take a machete to your application.
Putting
key references and text data in the same column? Sheesh.

If that's not an option, in addition to the approach you've taken, you could

also do a partial index on the appropriate numeric values:

CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
'^[0-9]{1,9}$';

However, this approach may be more/less effective that the segregation
approach you've already taken.

--
Josh Berkus
Aglio Database Solutions
San Francisco


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C496E0.EDB48D10
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable






RE: [SQL] Isnumeric function?





Josh,



I agree with the machete technique, unfortunately The str=
ucture is inplace and a work-around was required.



I created the Index you specified, however it chooses to =
run a seq scan on the column rather than a Index scan. How can you force it=
to use that Index..



CREATE INDEX idx_content_numeric ON botched_table(content=
) WHERE content ~ '^[0-9]{1,9}$';



select * from botched_table where content =3D 200::intege=
r



Theo

-----Original Message-----

From: Josh Berkus [=
mailto:josh@agliodbs.com]

Sent: Friday, 10 September 2004 4:46 AM

To: Theo Galanakis; pgsql-sql@postgresql.org

Subject: Re: [SQL] Isnumeric function?





Theo,



> Does anyone have any better suggestions???



Well, one suggestion would be to take a machete to your a=
pplication.  Putting

key references and text data in the same column? &n=
bsp; Sheesh.



If that's not an option, in addition to the approach you'=
ve taken, you could

also do a partial index on the appropriate numeric value=
s:



CREATE INDEX idx_content_numeric ON botched_table(content=
) WHERE content ~ '^[0-9]{1,9}$';



However, this approach may be more/less effective that th=
e segregation

approach you've already taken.



--

Josh Berkus

Aglio Database Solutions

San Francisco





____________________=
__________________________________________________
This email, including=
attachments, is intended only for the addressee
and may be confidential=
, privileged and subject to copyright. If you
have received this email =
in error, please advise the sender and delete
it. If you are not the in=
tended recipient of this email, you must not
use, copy or disclose its c=
ontent to anyone. You must not copy or
communicate to others content t=
hat is confidential or subject to
copyright, unless you have the consen=
t of the content owner.

------_=_NextPart_001_01C496E0.EDB48D10--

Re: Isnumeric function?

am 10.09.2004 06:48:33 von Sad

On Friday 10 September 2004 04:20, Theo Galanakis wrote:
> I was just thinking, wouldn't it be great if the pg community had a site
> where anyone could contribute their generic functions, or request for a
> particular function.

i vote positive.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Isnumeric function?

am 10.09.2004 07:39:33 von achill

O Theo Galanakis Ýãñáøå óôéò Sep 10, 2004 :

>
>
> Josh,
>
> I agree with the machete technique, unfortunately The structure is inplace
> and a work-around was required.
>
> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer

EXPLAIN ANALYZE is your friend.
VACUUM [FULL] ANALYZE also.
Try with 200::text
In the end if there is an option for the planner to use the index
but he doesn't, then maybe its not worth it.

>
> Theo
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Isnumeric function?
>
>
> Theo,
>
> > Does anyone have any better suggestions???
>
> Well, one suggestion would be to take a machete to your application.
> Putting
> key references and text data in the same column? Sheesh.
>
> If that's not an option, in addition to the approach you've taken, you could
>
> also do a partial index on the appropriate numeric values:
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> However, this approach may be more/less effective that the segregation
> approach you've already taken.
>
>

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Isnumeric function?

am 10.09.2004 08:43:46 von gsstark

Theo Galanakis writes:

> I created the Index you specified, however it chooses to run a seq scan on
> the column rather than a Index scan. How can you force it to use that
> Index..
>
> CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> '^[0-9]{1,9}$';
>
> select * from botched_table where content = 200::integer

You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
to match the clause in the partial index pretty closely.

perhaps you would find it convenient to make a view of
select * from botched_table where content ~ '^[0-9]{1,9}$'
and then just always select these values from that view.

Also the "::integer" is useless. It actually gets cast to text here anyways.
The index is on the text contents of the content column.

You might consider making the index a functional index on content::integer
instead. I suspect that would be faster and smaller than an index on the text
version of content:

slo=> create table botched_table (content text);
CREATE TABLE
slo=> create index idx_botched_table on botched_table ((content::integer)) where content ~ '^[0-9]{1,9}$';
CREATE INDEX
slo=> create view botched_view as (select content::integer as content_id, * from botched_table where content ~ '^[0-9]{1,9}$');
CREATE VIEW
slo=> explain select * from botched_view where content_id = 1;
QUERY PLAN
------------------------------------------------------------ ----------------------------
Index Scan using idx_botched_table on botched_table (cost=0.00..3.72 rows=3 width=32)
Index Cond: ((content)::integer = 1)
Filter: (content ~ '^[0-9]{1,9}$'::text)
(3 rows)


--
greg


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: Isnumeric function?

am 10.09.2004 09:02:18 von gsstark

Greg Stark writes:

> Theo Galanakis writes:
>
> > I created the Index you specified, however it chooses to run a seq scan on
> > the column rather than a Index scan. How can you force it to use that
> > Index..
> >
> > CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~
> > '^[0-9]{1,9}$';
> >
> > select * from botched_table where content = 200::integer
>
> You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
> to match the clause in the partial index pretty closely.

Well this is weird. I tried to come up with a cleaner way to arrange this than
the view I described before using a function. But postgres isn't using the
partial index when it seems it ought to be available.

When I say it has to match "pretty closely" in this case I think it would have
to match exactly, however in the case of simple range operators postgres knows
how to figure out implications. Ie, "where a>1" should use a partial index
built on "where a>0".

slo=> create table test (a integer);
CREATE TABLE
slo=> create index idx_text on test (a) where a > 0;
CREATE INDEX
slo=> explain select * from test where a > 0;
QUERY PLAN
------------------------------------------------------------ ------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 0)
(2 rows)

slo=> explain select * from test where a > 1;
QUERY PLAN
------------------------------------------------------------ ------------
Index Scan using idx_text on test (cost=0.00..17.50 rows=334 width=4)
Index Cond: (a > 1)
(2 rows)



That's all well and good. But when I tried to make a version of your situation
that used a function I found it doesn't work so well with functional indexes:



slo=> create function test(integer) returns integer as 'select $1' language plpgsql immutable;
CREATE FUNCTION
slo=> create index idx_test_2 on test (test(a)) where test(a) > 0;
CREATE INDEX
slo=> explain select test(a) from test where test(a) > 0;
QUERY PLAN
------------------------------------------------------------ --------------
Index Scan using idx_test_2 on test (cost=0.00..19.17 rows=334 width=4)
Index Cond: (test(a) > 0)
(2 rows)

slo=> explain select test(a) from test where test(a) > 1;
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..25.84 rows=334 width=4)
Filter: (test(a) > 1)
(2 rows)


I can't figure out why this is happening. I would think it has something to do
with the lack of statistics on functional indexes except a) none of the tables
is analyzed anyways and b) the estimated row count is the same anyways.


--
greg


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Isnumeric function?

am 10.09.2004 15:59:35 von tgl

Greg Stark writes:
> That's all well and good. But when I tried to make a version of your
> situation that used a function I found it doesn't work so well with
> functional indexes:
> ...
> I can't figure out why this is happening.

You're using 7.3 or older?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Isnumeric function?

am 10.09.2004 17:33:05 von gsstark

Tom Lane writes:

> Greg Stark writes:
> > That's all well and good. But when I tried to make a version of your
> > situation that used a function I found it doesn't work so well with
> > functional indexes:
> > ...
> > I can't figure out why this is happening.
>
> You're using 7.3 or older?

7.4.3.


--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org